<?php 
	class Topics extends CI_Model{

	 	function __construct(){
        	parent::__construct();
    	}

    	function get_chapter($conditions = array()){
            $where = "WHERE 1=1";
            if(isset($conditions['subject_id'])){
                // $where .= " AND subject_id = '{$conditions['subject_id']}'";
            }
    		$sql = "SELECT * FROM {$this->db->dbprefix}b_book_chapter $where";
    		$result = $this->db->query($sql)->result_array();
    		return $result;
    	}

        function get_topic_list($limit = 0, $offset = 0, $conditions = array()){
            $where = " 1 = 1 ";
            if(!empty($conditions['subject_id'])){
                $where .= " AND t.subject_id='{$conditions['subject_id']}' ";
            }
            if(!empty($conditions['chapter_id'])){
                $where .= " AND t.chapter_id='{$conditions['chapter_id']}' ";
            }
            if(!empty($conditions['type'])){
                $where .= " AND t.type='{$conditions['type']}' ";
            }
            if(!empty($conditions['difficulty_level'])){
                $where .= " AND t.difficulty_level='{$conditions['difficulty_level']}' ";
            }

            if(!empty($conditions['subject_text'])){
                $where .= " AND t.subject_text LIKE '%{$conditions['subject_text']}%'";
            }

            if(!empty($conditions['start_time'])){
                $start_time = strtotime($conditions['start_time']);
                $where .= " and t.add_time >= '{$start_time}' ";
            }

            if(!empty($conditions['end_time'])){
                $end_time = strtotime($conditions['end_time']) + 3600 * 24;
                $where .= " and t.add_time <= '{$end_time}' ";
            }

            $sql = "SELECT SQL_CALC_FOUND_ROWS t.*,sub.name as subject_name,book.title as book_name FROM {$this->db->dbprefix}tk_topics t
            LEFT JOIN {$this->db->dbprefix}b_subject sub on sub.id = t.subject_id
            LEFT JOIN {$this->db->dbprefix}b_book book on book.id = t.book_id 
            WHERE $where ORDER BY id DESC ";
            if($limit > 0 || $offset > 0){
                $sql .= "LIMIT $limit, $offset";
            }
            $result = $this->db->query($sql)->result_array();
            $row_num =  $this->db->query('SELECT FOUND_ROWS() as row_num;')->row_array();
            return array("count" => $row_num['row_num'], "result" => $result);
        }

        function get_topic_reuslt($topic_id){
            $sql = "SELECT * FROM {$this->db->dbprefix}tk_topic_result WHERE topic_item_id = $topic_id ORDER BY id ASC";
            $result = $this->db->query($sql)->result_array();
            return $result;
        }

        function get_topic_analys($topic_id){
            $sql = "SELECT * FROM {$this->db->dbprefix}tk_topic_analys WHERE topic_item_id = $topic_id ORDER BY id ASC ";
            $result = $this->db->query($sql)->result_array();
            return $result;
        }

        function get_topic_analys_result($aid){
            $sql = "SELECT * FROM {$this->db->dbprefix}tk_topic_analys_result WHERE aid = $aid ORDER BY id ASC ";
            $result = $this->db->query($sql)->result_array();
            return $result;
        }

        function get_test_volume_templates($schoo_id){
            $sql = "SELECT * FROM {$this->db->dbprefix}tk_test_volume_templates WHERE school_id = 0 ORDER BY id DESC";
            $result = $this->db->query($sql)->result_array();
            return $result;
        }

        function get_topic_by_id($topic_id){
            $sql = "SELECT id,type,subject_text,analysis,difficulty_level FROM {$this->db->dbprefix}tk_topics WHERE id IN($topic_id)";
            $result = $this->db->query($sql)->result_array();
            return $result;
        }

        function get_topic_result_by_id($topic_id){
            $sql = "SELECT * FROM {$this->db->dbprefix}tk_topic_result WHERE topic_item_id IN($topic_id)";
            $result = $this->db->query($sql)->result_array();
            return $result;
        }

        function get_rand_topic($conditions = array()){
            $where = "WHERE t1.id >= t2.id ";

            if(!empty($conditions['type'])){
                $where .= "AND t1.type = '{$conditions['type']}' ";  
            }

            if(!empty($conditions['notin_ids'])){
                $notin_ids = implode(",", $conditions['notin_ids']);
                $where .= "AND t1.id NOT IN($notin_ids) ";  
            }

            if(isset($conditions['school_id'])){
                $where .= "AND t1.school_id = '{$conditions['school_id']}' ";  
            }

            if(!empty($conditions['subject_id'])){
                $where .= "AND t1.subject_id = '{$conditions['subject_id']}' ";  
            }
            
            $sql = "SELECT t1.id,t1.subject_text FROM {$this->db->dbprefix}tk_topics AS t1 JOIN (SELECT ROUND(RAND() * ((SELECT MAX(id) FROM {$this->db->dbprefix}tk_topics)-(SELECT MIN(id) FROM {$this->db->dbprefix}tk_topics))+(SELECT MIN(id) FROM {$this->db->dbprefix}tk_topics)) AS id) AS t2 $where ORDER BY t1.id LIMIT 1";
            $result = $this->db->query($sql)->row_array();
            return $result;
        }

        function get_topic_items($topic_id){
            $sql = "SELECT * FROM {$this->db->dbprefix}tk_topic_items WHERE topic_id='{$topic_id}'";
            $result = $this->db->query($sql)->result_array();
            return $result;
        }

        function get_result_by_topic_id($topic_id){
            $sql = "SELECT * FROM {$this->db->dbprefix}tk_topic_result WHERE topic_id='{$topic_id}'";
            $result = $this->db->query($sql)->result_array();
            return $result;
        }
	}